Imports System.Text
Imports System.Data
Imports System.Data.OleDb

Public Class dbStructure



    Public Class relacion

    End Class

    Public Shared Function GetTables(ByVal conn As OleDbConnection) As DataTable
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If

            Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                                                                    New Object() {Nothing, Nothing, Nothing, "TABLE"})
            Return schemaTable

        Catch olx As OleDbException
            Select Case olx.ErrorCode
                Case -2147467259 ' archivo no encontrado
                    MsgBox("No es posible encontrar el archivo")


            End Select
        Catch ex As Exception

        Finally
            If conn Is Nothing Then
            Else
                If conn.State <> ConnectionState.Closed Then
                    conn.Close()

                End If
            End If


        End Try

    End Function


    Public Shared Sub relacionesDb(ByVal cn As OleDbConnection)

        Dim row As DataRow
        Dim adp As New OleDbDataAdapter("", cn)


        'we can put restrictions on the data we want to recieve

        Dim restrictions As Object() = New Object() {Nothing, Nothing, Nothing, Nothing}



        'this is the Foreign key data we need to create relations

        Dim schemaTable As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, restrictions)

        ' para claves primarias
        'dtPKs = Me.mConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, Nothing)


        'go through all the foreign keys displayed

        For Each row In schemaTable.Rows

            Dim strTable As String = row("PK_TABLE_NAME")
            Dim strChild As String = row("FK_TABLE_NAME")

            Dim strParentColName As String = row("PK_COLUMN_NAME")
            Dim strChildColName As String = row("FK_COLUMN_NAME")


            '	newRelation.ParentClass = DomainObjectSchema.Instance.Classes.FindByName(PK_TABLE_NAME);
            '		newRelation.ParentMember = newRelation.ParentClass.FindByName(PK_COLUMN_NAME);
            '	newRelation.ChildClass = DomainObjectSchema.Instance.Classes.FindByName(FK_TABLE_NAME);
            '		newRelation.ChildMember = newRelation.ParentClass.FindByName(FK_COLUMN_NAME);
            '	newRelation.Name = String.Format("RELATION_{0}_{1}_{2}_{3}", PK_TABLE_NAME, PK_COLUMN_NAME, FK_TABLE_NAME, FK_COLUMN_NAME);


            'the relation name that will be created

            Dim strRelationName As String = strTable + "_" + strParentColName + "_" + strChild + "_" + strChildColName



            'create any non existing tables along with key information

            ' CreateAndfFillSchema(ds, adp, strTable)

            ' CreateAndfFillSchema(ds, adp, strChild)



            '   Try

            'Dim PrimaryColumn As DataColumn = ds.Tables(strTable).Columns(strParentColName)
            ' Dim ChildColumn As DataColumn = ds.Tables(strChild).Columns(strChildColName)

            ' ds.Relations.Add(strRelationName, PrimaryColumn, ChildColumn)

            ' Catch e As Exception

            'well get here if there's already such as relation

            'but we want to continue anyway

            '  End Try

        Next

    End Sub

    


    Private Shared Sub CreateAndfFillSchema(ByVal ds As DataSet, ByVal adp As OleDbDataAdapter, ByVal TableName As String)

        If Not ds.Tables.Contains(TableName) Then

            adp.SelectCommand.CommandText = "SELECT * FROM " + TableName

            adp.FillSchema(ds, SchemaType.Source, TableName)

        End If

    End Sub


    ' saca las claves primarias para una tabla
    Public Shared Function GetKeys(ByVal conn As OleDbConnection, ByVal tablax As lb.tabla) As Boolean
        Try
            '  conn.Open()
            Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, _
                    New Object() {Nothing, Nothing, tablax.nombre})

            'List the column name from each row in the schema table.
            Dim i As Integer

            For i = 0 To schemaTable.Rows.Count - 1
                ' nombre de la clave
                'schemaTable.Rows(i).Item(3).ToString
                ' como ya tenemos los campos dibujados
                Dim campito As lb.campo
                For Each campito In tablax.campos

                    If campito.nombre = schemaTable.Rows(i).Item(3).ToString Then
                        campito.isKey = True

                    End If
                Next


            Next
        Catch ex As Exception
        Finally
            ' conn.Close()
        End Try

    End Function
    ' Public Function getIndex()
    ' Get the table's index data.
    '    Dim dt_index As System.Data.DataTable = _
    '        DBConn.GetOleDbSchemaTable( _
    '            OleDb.OleDbSchemaGuid.Indexes, _
    '            New Object() {Nothing, Nothing, Nothing, _
    '                Nothing, table_name})

    '    ' Headers.
    '    Dim index_properties() As String = _
    '        Split("Name,Primary,Unique,Column", ",")
    '    For col_num As Integer = 0 To _
    '        index_properties.Length - 1
    '        excel_worksheet.Cells(row_num, col_num + 1) = _
    '            index_properties(col_num)
    '    Next col_num
    '    With excel_worksheet.Range("A" + row_num, "Z" + _
    '        row_num).Font
    '        .Bold = True
    '    End With
    '    row_num += 1

    '    ' Display the table's index's information.
    '    For Each dr_index As DataRow In dt_index.Rows
    '        excel_worksheet.Cells(row_num, 1) = _
    '            dr_index("INDEX_NAME").ToString
    '        excel_worksheet.Cells(row_num, 2) = _
    '            dr_index("PRIMARY_KEY").ToString
    '        excel_worksheet.Cells(row_num, 3) = _
    '            dr_index("UNIQUE").ToString
    '        excel_worksheet.Cells(row_num, 4) = _
    '            dr_index("COLUMN_NAME").ToString
    '        row_num += 1
    '    Next dr_index
    'Next dr_table
    'End Function
    '

    Public Shared Function getForeignKeysForTable(ByVal conn As OleDbConnection, ByVal tablax As lb.tabla) As Boolean
        ' Foreign keys.

        Try
            '  conn.Open()
            Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, _
                    New Object() {Nothing, Nothing, tablax.nombre})

            'List the column name from each row in the schema table.
            Dim i As Integer

            Dim SB As New StringBuilder
            For i = 0 To schemaTable.Rows.Count - 1
                ' nombre de la clave
                'schemaTable.Rows(i).Item(3).ToString
                ' como ya tenemos los campos dibujados


                ' PARA SABER LO QUE SALE..
                ' For Each cadena As Object In schemaTable.Rows(i).ItemArray
                'SB.Append(cadena)
                'SB.Append(Environment.NewLine)


                Dim nombre As New String("")
                Dim descripcion As New String("")

                Dim tablaPadre As New String("")
                Dim campoPadre As New String("")

                Dim tablaHijo As New String("")
                Dim campoHijo As New String("")

                Dim update As Boolean = False
                Dim delete As Boolean = False

                nombre = schemaTable.Rows(i)("FK_NAME").ToString
                descripcion = schemaTable.Rows(i)("PK_NAME").ToString

                tablaPadre = schemaTable.Rows(i)("PK_TABLE_NAME").ToString
                campoPadre = schemaTable.Rows(i)("PK_COLUMN_NAME").ToString

                tablaHijo = schemaTable.Rows(i)("FK_TABLE_NAME").ToString
                campoHijo = schemaTable.Rows(i)("FK_COLUMN_NAME").ToString


                If schemaTable.Rows(i)("UPDATE_RULE").ToString.IndexOf("CASCADE") <> -1 Then
                    update = True
                End If


                If schemaTable.Rows(i)("DELETE_RULE").ToString.IndexOf("CASCADE") <> -1 Then
                    delete = True
                End If



                Dim fk As New lb.foreignKey(nombre, descripcion, tablaPadre, campoPadre, tablaHijo, campoHijo, update, delete)
                ' tablax.foreignKeys.Add(fk)
                ' lo pongo tambien en el proyecto
                ' proyectoActual.foreignKeys.Add(fk)
                fk = Nothing

                ' Next

            Next
        Catch ex As Exception
        Finally
            ' conn.Close()
        End Try


    End Function

    Public Shared Function getForeignKeysForProyect(ByVal conn As OleDbConnection) As Boolean
        ' Foreign keys.

        Try
            '  conn.Open()
            proyectoActual.foreignKeys.Clear()

            Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, _
                    New Object() {Nothing, Nothing, Nothing})

            'List the column name from each row in the schema table.
            Dim i As Integer

            Dim SB As New StringBuilder
            For i = 0 To schemaTable.Rows.Count - 1
                ' nombre de la clave
                'schemaTable.Rows(i).Item(3).ToString
                ' como ya tenemos los campos dibujados


                ' PARA SABER LO QUE SALE..
                ' For Each cadena As Object In schemaTable.Rows(i).ItemArray
                'SB.Append(cadena)
                'SB.Append(Environment.NewLine)


                Dim nombre As New String("")
                Dim descripcion As New String("")

                Dim tablaPadre As New String("")
                Dim campoPadre As New String("")

                Dim tablaHijo As New String("")
                Dim campoHijo As New String("")

                Dim update As Boolean = False
                Dim delete As Boolean = False

                nombre = schemaTable.Rows(i)("FK_NAME").ToString
                descripcion = schemaTable.Rows(i)("PK_NAME").ToString

                tablaPadre = schemaTable.Rows(i)("PK_TABLE_NAME").ToString
                campoPadre = schemaTable.Rows(i)("PK_COLUMN_NAME").ToString

                tablaHijo = schemaTable.Rows(i)("FK_TABLE_NAME").ToString
                campoHijo = schemaTable.Rows(i)("FK_COLUMN_NAME").ToString


                If schemaTable.Rows(i)("UPDATE_RULE").ToString.IndexOf("CASCADE") <> -1 Then
                    update = True
                End If


                If schemaTable.Rows(i)("DELETE_RULE").ToString.IndexOf("CASCADE") <> -1 Then
                    delete = True
                End If


                'SqlServer = 0
                'SqlServerMobile = 1
                'Access = 2
                'Oracle = 3
                'DB2 = 4
                'MySql = 5
                'ISeries = 6
                'Excel = 7
                'TextCsv = 8
                Select Case proyectoActual.tipoBd
                    ' sql server
                    Case "0"


                    Case Else

                End Select


                Dim fk As New lb.foreignKey(nombre, descripcion, tablaPadre, campoPadre, tablaHijo, campoHijo, update, delete)

                ' lo pongo tambien en el proyecto
                proyectoActual.foreignKeys.Add(fk)
                fk = Nothing

                ' Next

            Next
        Catch ex As Exception
        Finally
            ' conn.Close()
        End Try


    End Function


    '     /// <summary>
    ' /// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbtable_constraints_rowset.asp
    ' /// Restriction columns: CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_CATALOG,
    '///             TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE ("UNIQUE", "PRIMARY KEY", "FOREIGN KEY", "CHECK")
    '  ///     Schema columns: IS_DEFERRABLE, INITIALLY_DEFERRED, DESCRIPTION 
    '  /// </summary>
    Public Shared Function getConstraints(ByVal conn As OleDbConnection, ByVal tablax As lb.tabla) As Boolean
        ' Foreign keys.

        Try
            '  conn.Open()DBSCHEMA_TABLE_CONSTRAINTS
            Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Table_Constraints, _
                    New Object() {Nothing, Nothing, tablax.nombre})

            'List the column name from each row in the schema table.
            Dim i As Integer

            Dim SB As New StringBuilder
            For i = 0 To schemaTable.Rows.Count - 1
                ' nombre de la clave
                'schemaTable.Rows(i).Item(3).ToString
                ' como ya tenemos los campos dibujados


                ' PARA SABER LO QUE SALE..
                For Each cadena As String In schemaTable.Rows(i).ItemArray
                    SB.Append(cadena)
                    SB.Append(Environment.NewLine)

                Next


            Next
        Catch ex As Exception
        Finally
            ' conn.Close()
        End Try

    End Function

End Class

